package com.atguigu.realtime.gmallpublisher.mapper;

import com.atguigu.realtime.gmallpublisher.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ProductStatsMapper {
    // toYYYYMMDD 把DateTime类型的数据转成数字  2020-03-26 11:11:11 -> 20200326
    // 获取订单总额
    @Select("select " +
        "sum(order_amount) " +
        "from product_stats_2021 " +
        "where toYYYYMMDD(stt)=#{date}")
    BigDecimal getGMV(int date);
    
    /*
      SELECT
            tm_id,
            tm_name,
            sum(order_amount) AS order_amount
    FROM product_stats_2021
    WHERE toYYYYMMDD(stt) = '20210326'
    GROUP BY
        tm_id,
        tm_name
    ORDER BY order_amount DESC
    LIMIT 5
     */
    
    @Select("SELECT\n" +
        "            tm_id,\n" +
        "            tm_name,\n" +
        "            sum(order_amount) AS order_amount\n" +
        "    FROM product_stats_2021\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY\n" +
        "        tm_id,\n" +
        "        tm_name\n" +
        "    ORDER BY order_amount DESC\n" +
        "    LIMIT #{limit}")
    List<ProductStats> getProductStatsByTM(@Param("date") int date,
                                           @Param("limit") int limit);
    
    
    @Select("SELECT\n" +
        "            spu_id,\n" +
        "            spu_name,\n" +
        "            sum(order_amount) AS order_amount\n, " +
        "            sum(order_ct) AS order_ct\n " +
        "    FROM product_stats_2021\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY\n" +
        "        spu_id,\n" +
        "        spu_name\n" +
        "    having order_amount > 0\n" +
        "    ORDER BY order_amount DESC\n" +
        "    LIMIT #{limit}")
    List<ProductStats> getProductStatsBySpu(@Param("date") int date,
                                           @Param("limit") int limit);
    
    
    
     @Select("SELECT\n" +
        "            category3_id,\n" +
        "            category3_name,\n" +
        "            sum(order_amount) AS order_amount\n, " +
        "            sum(order_ct) AS order_ct\n " +
        "    FROM product_stats_2021\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY\n" +
        "        category3_id,\n" +
        "        category3_name\n" +
        "    having order_amount > 0\n" +
        "    ORDER BY order_amount DESC\n" +
        "    LIMIT #{limit}")
    List<ProductStats> getProductStatsByC3(@Param("date") int date,
                                           @Param("limit") int limit);
    
    
    
    
    
}
